# 建议先设置配置文件： config.py 并保存，再开始操作

import os
from openpyxl import load_workbook


class R:
    AppName = "x01.ExcelHelper"
    Version = "0.0.1"

    # 自动录入配置
    A_FileName = r"示例/自动录入示例.xlsx"  # 文件名， r 为原始字符串，设置路径不可省
    A_SheetName = "2.01"  # 表名
    A_IsAdd = True  # 是否添加录入, True： 添加录入； False： 调减录入，需关闭输入法
    A_FirstClient = True  # 是否一户多码, True： 一店多书； False： 一书多店
    A_ClientRow = "3"  # 客户编号行
    A_CodeCol = "5"  # 征订代码列
    A_CodeBits = "6"  # 代码位数, 教材："6"； 教辅：None 或 "0" 或 "-1"
    A_StartRow = "6"  # 起始行
    A_EndRow = "300"  # 结束行
    A_StartCol = "7"  # 起始列
    A_EndCol = "100"  # 结束列

    # 填充类别, 生成销售确认函和账目表
    F_Filename = r"示例/确认函示例.xlsx"  # 文件名， r 为原始字符串，设置路径不可省
    F_SaleSheetName = "xs"  # 销售表名
    F_BackSheetName = "xt"  # 销退表名
    F_SaleBackCategoryCol = "9"  # 销售销退类别列
    F_OrgSheetName = "明细"  # 原始表名
    F_OrgClientRow = "3"  # 原始客户编号行
    F_OrgCategoryCol = "1"  # 原始类别列
    F_OrgDateCol = "2"  # 原始日期列
    F_OrgStartRow = "6"  # 原始起始行
    F_OrgEndRow = "500"  # 原始结束行
    F_OrgStartCol = "3"  # 原始起始列
    F_OrgEndcol = "100"  # 原始结束列

    F_SaleTemplateFilename = r"示例/输出/确认函模板.xlsx"  # 销售确认函模板文件名
    F_AccountTemplateFilename = r"示例/输出/做账模板.xlsx"  # 做账模板文件名

    # 生成免费申报表
    R_OrgFilename = r"示例/免费申报示例.xlsx"  # 原始表文件
    R_TempFilename = r"示例/输出/免费申报模板.xlsx"  # 模板表文件

    R_OrgSheet1Name = "1.01"  # 原始表1名称
    R_OrgSheet1StartRow = "6"  # 原始表1起始行
    R_OrgSheet1EndRow = "159"  # 原始表1结束行
    R_OrgSheet1StartCol = "7"  # 原始表1起始列
    R_OrgSheet1EndCol = "32"  # 原始表1结束列
    R_OrgSheet1ClientRow = "5"  # 原始表1客户名称行
    R_OrgSheet1CodeCol = "5"  # 原始表1征订代码列
    R_OrgSheet2Name = "1.02"  # 原始表2名称
    R_OrgSheet2StartRow = "6"  # 原始表2起始行
    R_OrgSheet2EndRow = "159"  # 原始表2结束行
    R_OrgSheet2StartCol = "7"  # 原始表2起始列
    R_OrgSheet2EndCol = "46"  # 原始表2结束列
    R_OrgSheet2ClientRow = "5"  # 原始表2客户名称行
    R_OrgSheet2CodeCol = "5"  # 原始表2征订代码列
    R_TempSheet0Name = "附件1"  # 模板表0名称
    R_TempSheet0ClientRow = "4"  # 模板表0客户行
    R_TempSheet0ClientCol = "1"  # 模板表0客户列
    R_TempSheet1Name = "附件2"  # 模板表1名称
    R_TempSheet1CodeCol = "7"  # 模板表1代码列
    R_TempSheet1StartRow = "5"  # 模板表1起始行
    R_TempSheet1EndRow = "126"  # 模板表1结束行
    R_TempSheet1FillCol = "5"  # 模板表1填充列
    R_TempSheet2Name = ""  # 模板表2名称， 为空时不操作
    R_TempSheet2CodeCol = "6"  # 模板表2代码列
    R_TempSheet2StartRow = "6"  # 模板表2起始行
    R_TempSheet2EndRow = "23"  # 模板表2结束行
    R_TempSheet2FillCol = "4"  # 模板表2填充列
    R_TempSheet3Name = ""  # 如下同 2
    R_TempSheet3CodeCol = "6"
    R_TempSheet3StartRow = "7"
    R_TempSheet3EndRow = "8"
    R_TempSheet3FillCol = "4"
    R_TempSheet4Name = ""
    R_TempSheet4CodeCol = "5"
    R_TempSheet4StartRow = "6"
    R_TempSheet4EndRow = "6"
    R_TempSheet4FillCol = "4"
    R_TempSheet5Name = ""
    R_TempSheet5CodeCol = "6"
    R_TempSheet5StartRow = "7"
    R_TempSheet5EndRow = "9"
    R_TempSheet5FillCol = "4"
    R_TempSheet6Name = ""
    R_TempSheet6CodeCol = "6"
    R_TempSheet6StartRow = "7"
    R_TempSheet6EndRow = "9"
    R_TempSheet6FillCol = "4"

    # 生成责任书
    T_OrgFilename = r"示例/责任书示例.xlsx"  # 原始文件名
    T_TempFilename = r"示例/输出/责任书模板.xlsx"  # 模板表文件

    T_OrgSheetName = "九丘"  # 原始表1名称
    T_OrgSheetStartRow = "2"  # 原始表1起始行
    T_OrgSheetEndRow = "50000"  # 原始表1结束行
    T_IsTask = False  # 选择生成责任书
    T_TempSheetname = "T"  # 模板表名称

    # 填充列二选一 1.for 九丘
    Org_filename = r"示例/填充列示例.xlsx"  # 原始文件名
    Org_sheetname = "学校"  # 原始表名
    Org_client_id = "3"  # 原始客户编号： 填充汇总 时为行， 填充系统 时为列
    Org_code_col = "1"  # 原始代码列
    Org_start_row = "2"  # 原始起始行
    Org_end_row = "400"  # 原始结束行
    Org_start_col = "5"  # 原始起始列
    Org_end_col = "200"  # 原始结束列
    Dest_filename = r""  # 目标文件名， 可与原文件相同
    Dest_sheetname = "九丘"  # 目标表名
    Dest_client_row = "3"  # 目标客户编号行
    Dest_code_col = "3"  # 目标代码列
    Dest_start_row = "2"  # 目标起始行
    Dest_end_row = "20000"  # 目标结束行
    Dest_start_col = "11"  # 目标起始列
    Dest_end_col = "200"  # 目标结束列
    Dest_price_col = "6"  # 目标价格列，填充金额合计使用
    Dest_sum_row = "130"  # 目标金额行，填充金额合计使用
    Dest_is_sum = False  # True: 填充单列时累加； False：不累加
    Dest_is_sort = False  #  True: 排序, False: 不排序

    # 填充列二选一 2.for 汇总
    # Org_filename = r"示例/填充列示例.xlsx"  # 原始文件名
    # Org_sheetname = "教本"  # 原始表名
    # Org_client_id = "3"  # 原始客户编号： 填充汇总 时为行， 填充系统 时为列
    # Org_code_col = "5"  # 原始代码列
    # Org_start_row = "6"  # 原始起始行
    # Org_end_row = "200"  # 原始结束行
    # Org_start_col = "7"  # 原始起始列
    # Org_end_col = "200"  # 原始结束列
    # Dest_filename = r""  # 目标文件名， 可与原文件相同
    # Dest_sheetname = "1.12"  # 目标表名
    # Dest_client_row = "3"  # 目标客户编号行
    # Dest_code_col = "5"  # 目标代码列
    # Dest_start_row = "6"  # 目标起始行
    # Dest_end_row = "200"  # 目标结束行
    # Dest_start_col = "7"  # 目标起始列
    # Dest_end_col = "200"  # 目标结束列
    # Dest_price_col = "6"  # 目标价格列，填充金额合计使用
    # Dest_sum_row = "130"  # 目标金额行，填充金额合计使用
    # Dest_is_sum = False  # True: 填充列时累加； False：不累加
    # Dest_is_sort = False #  True: 排序, False: 不排序

    @staticmethod
    def toInt(i):
        temp = 0
        try:
            temp = int(i)
        except:
            return None
        return temp


class ExcelConfig:
    """基本配置"""

    @staticmethod
    def GenClients(filename, sheetname="T"):
        """
        根据特定模板生成 Clients 元组
        """
        out_filename = os.path.join(os.path.dirname(filename), "temp.txt")
        wb = load_workbook(filename, data_only=True)
        sheet = wb[sheetname]
        i = 1
        s = ""
        for v in sheet.values:
            if v[1] is None or v[1] == "客户编号":
                continue
            s += (
                "("
                + str(i)
                + ","
                + str(v[1])
                + ",'"
                + str(v[2])
                + "'"
                + ",'"
                + str(v[3])
                + "'),"
            )
            i += 1
        with open(out_filename, "w") as f:
            f.write(s)
        print("OK! " + filename)

    # 客户新增时，如下配置需作相应更改
    Clients = [
        (1, 90003734, "区一中", "曾都区第一中学"),
        (2, 90003735, "区二中", "曾都区第二中学"),
        (3, 90003718, "八角楼", "曾都区东城八角楼中心学校"),
        (4, 90003668, "蒋家岗", "曾都区东城蒋家岗学校"),
        (5, 90003678, "聚奎门", "曾都区东城聚奎门学校"),
        (6, 90003676, "文峰塔", "曾都区东城文峰塔学校"),
        (7, 90003713, "五丰小学", "曾都区北郊五丰小学"),
        (8, 90012582, "五丰中学", "曾都区五丰中学"),
        (9, 90003703, "五眼桥小学", "曾都区北郊五眼桥小学"),
        (10, 90003711, "五眼桥中学", "曾都区北郊五眼桥中学"),
        (11, 90010427, "随小北区", "随州市外国语北校区（小学部）"),
        (12, 90007398, "随中北区", "随州市外国语北校区（初中部）"),
        (13, 90010428, "随小南区", "随州市外国语南校区（小学部）"),
        (14, 90009777, "随中南区", "随州市外国语南校区（初中部）"),
        (15, 90003614, "实中", "曾都区实验中学"),
        (16, 90003603, "东外", "曾都区东关外国语学校"),
        (17, 90003542, "金太阳", "随州金太阳学校"),
        (18, 90003699, "白云双语", "曾都区白云双语学校"),
        (19, 90010061, "文峰小学", "随州文峰小学"),
        (20, 90003710, "文峰中学", "曾都区文峰完全中学"),
        (21, 90003509, "清河路", "曾都区清河路学校"),
        (22, 90003496, "岁丰", "曾都区西城岁丰中心学校"),
        (23, 90008875, "两水", "曾都区北郊两水学校"),
        (24, 90003683, "白云墩", "曾都区南郊白云墩小学"),
        (25, 90003688, "擂小", "曾都区南郊擂鼓墩小学"),
        (26, 90003715, "擂中", "曾都区南郊擂鼓墩中心学校"),
        (27, 90012401, "鹿鹤", "随州市曾都区鹿鹤学校"),
        (28, 90003693, "马家榨", "曾都区南郊马家榨学校"),
        (29, 90011684, "南小", "南郊中心小学"),
        (30, 90003525, "东关学校", "曾都区东关学校"),
        (31, 90012258, "湖小", "随州市曾都区白云湖小学部"),
        (32, 90003530, "编钟小学", "曾都区编钟小学"),
        (33, 90012581, "编钟中学", "曾都区编钟中学"),
        (34, 90003495, "铁树", "曾都区铁树学校"),
        (35, 90003522, "实小", "曾都区实验小学"),
        (36, 90003537, "铁路", "随州铁路小学"),
        (37, 90010062, "兴汉东", "随州市曾都区兴汉东学校"),
        (38, 90011084, "阳光", "随州市阳光学校"),
        (39, 90003463, "府河", "曾都区府河镇中心学校"),
        (40, 90003461, "万店", "曾都区万店镇中心学校"),
        (41, 90003470, "洛阳", "曾都区洛阳镇中心学校"),
        (42, 90003466, "何店", "曾都区何店镇中心学校"),
        (43, 90003719, "市一中", "随州市第一中学"),
        (44, 90008876, "欧阳修中学", "随州市欧阳修中学"),
        (45, 90003721, "市二中", "随州市第二中学"),
        (46, 1185026, "汉东本部", "随州市汉东中学（本部）"),
        (47, 90003839, "汉东中学", "随州市汉东中学"),
        (48, 90008877, "技师学院", "随州市技师学院"),
        (49, 90007576, "教育学校", "湖北现代教育学校"),
        (50, 900345410, "行知高中", "随州市行知高级中学"),
        (51, 90003732, "白云高中", "随州市白云高级中学"),
        (52, 90012580, "机电工程", "随州机电工程学校"),
        (53, 90012664, "经开", "随州经开实验技工学校"),
        (54, 202022080401, "楚北", "随州市楚北高级中学"),
        (55, 90012665, "盈瑞", "随州市盈瑞实验高中"),
        (56, 90003451, "淅河", "高新区淅河镇中心学校"),
        (57, 90011667, "胜利", "随州胜利学校"),
        (58, 90012275, "季梁", "随州市季梁学校"),
        (59, 212023122101, "文帝", "随州市文帝学校"),
        (60, 90003736, "县二中", "随县第二高级中学"),
        (61, 90003737, "县一中", "随县第一高级中学"),
        (62, 90012666, "丹外", "随县丹外高级中学"),
        (63, 90003448, "厉山", "随县厉山镇中心学校"),
        (64, 212022022001, "烈山湖", "随县烈山湖学校"),
        (65, 90011609, "炎帝", "随县炎帝学校"),
        (66, 90012274, "神农", "随县神农学校"),
        (67, 90003457, "安居", "随县安居镇中心学校"),
        (68, 90003472, "新街", "随县新街镇中心学校"),
        (69, 90003480, "万和", "随县万和镇中心学校"),
        (70, 90007520, "尚市", "随县尚市镇中心学校"),
        (71, 90006382, "唐镇", "随县唐镇中心学校"),
        (72, 90003401, "吴山", "随县吴山中心学校"),
        (73, 90003492, "万福", "随县万福中心学校"),
        (74, 90003459, "殷店", "随县殷店镇中心学校"),
        (75, 90003486, "高城", "随县高城镇中心学校"),
        (76, 90003464, "均川", "随县均川镇中心学校"),
        (77, 90003490, "柳林", "随县柳林镇中心学校"),
        (78, 90003476, "三里岗", "随县三里岗镇中心学校"),
        (79, 90003491, "长岗", "随县长岗镇中心学校"),
        (80, 90003453, "洪山", "随县洪山镇中心学校"),
        (81, 90003455, "环潭", "随县环潭镇中心学校"),
        (82, 90003484, "草店", "随县草店镇中心学校"),
        (83, 90003478, "小林", "随县小林镇中心学校"),
        (84, 90003483, "淮河", "随县淮河镇中心学校"),
        (85, 2, "职院", "随州市职业技术学校"),
        (86, 90003856, "曾都特校", "曾都区特殊教育学校"),
        (87, 212023122102, "随县特校", "随县特殊教育学校"),
        (88, 90012402, "区教研室", "随州市曾都区教研室"),
        (89, 90011027, "县教育局", "随县教育局"),
        (90, 90007029, "教材柜", "随州市教材柜"),
        
        (72, 90010120, "吴山", "随县吴山中心学校"),  # 吴山教辅
    ]

    @staticmethod
    def ClientInfos():
        clientInfo = {}
        for c in ExcelConfig.Clients:
            clientInfo[c[1]] = c
        return clientInfo

    @staticmethod
    def SelectId(id):
        if id == 90010120:  # 吴山教辅 client_id
            return 90003401
        return id

    def __init__(
        self,
        sheet_name=None,
        client_id=None,
        client_name=None,
        product_id=None,
        product_name=None,
        start_row=None,
        end_row=None,
        start_col=None,
        end_col=None,
        start_date=None,
        end_date=None,
        category=None,
        number=None,
        amount=None,
        discount=None,
        sale_id=None,
        sale_mode=None,
        sale_date=None,
        sale_type=None,
        free_fill_col=None,
    ):
        self.sheet_name = sheet_name
        self.client_id = client_id
        self.client_name = client_name
        self.product_id = product_id
        self.product_name = product_name
        self.start_row = start_row
        self.end_row = end_row
        self.start_col = start_col
        self.end_col = end_col
        self.start_date = start_date
        self.end_date = end_date
        self.category = category
        self.number = number
        self.amount = amount
        self.discount = discount
        self.sale_id = sale_id
        self.sale_mode = sale_mode
        self.sale_date = sale_date
        self.sale_type = sale_type
        self.free_fill_col = free_fill_col
